BigQuery に外部テーブルを定義して、GCS のファイルデータを SQL で参照してみた

#Google BigQuery

#Google Cloud Storage

みかみ

2020.04.13

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

AWS では S3 上のデータに直接 SQL クエリを実行できる Athena や、S3上のデータと Redshift のテーブルを結合できる Redshift Spectrum がありますが、GCP でも同様に BigQuery の external テーブルを使って GCS 上のデータを SQL で参照できるようなので、確認してみました。

外部データソースの概要｜ BigQuery ドキュメント

やりたいこと

GCS に格納したファイルに対して、BigQuery から SQL クエリを実行してデータを参照したい。

Cloud Storage データのクエリ｜ BigQuery ドキュメント

制限事項

BigQuery の external テーブルを作成する場合、外部データソースと BigQuery のデータセットは、同一リージョンで作成する必要があります。

また、データ整合性（クエリ実行中に外部データソースが更新された場合の挙動）は保証されなかったり、クエリ結果のキャッシュが効かないなどの制限事項があります。

external テーブルを作成

GCS に、以下のような CSV 形式のファイルデータをアップロードしました。

Jennifer,F,58375
Amanda,F,35821
Jessica,F,33921
Melissa,F,31636
Sarah,F,25755
（省略）

続いて、Python クライアントライブラリを使用した以下のコードで、GCS 上のファイルデータを参照する external テーブルを作成します。

from google.cloud import bigquery

client = bigquery.Client()
dataset_id = 'dataset_1'

dataset_ref = client.dataset(dataset_id)
table_id = "table_external"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("gender", "STRING"),
    bigquery.SchemaField("count", "INTEGER"),
]
table = bigquery.Table(dataset_ref.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://test-mikami/yob1980.txt"
]
table.external_data_configuration = external_config
table = client.create_table(table)
print("Created table {}".format(table_id))

(test_bq) [ec2-user@ip-10-0-43-239 ~]$ python create_external_table.py
Created table table_external

GCP 管理コンソールから、テーブルが作成できたことが確認できました。

（が、サイズと行数が「0」？ちゃんとデータ参照できるのかな？

external テーブルに対して SQL クエリを実行

作成した external テーブルに、SQL を実行してデータを参照してみます。

通常のテーブル同様、データが参照できました。

別のデータセットの通常テーブルとの JOIN も問題なく実行できるので、SQL で SELECT 文を実行する分には、通常のテーブルと同じように使えそうです。

なお、外部データソースなので、external テーブルに対して更新や削除などの DML は実行できません。

INFORMATION_SCHEMA とメタテーブルから external テーブル情報を取得する

まずは INFORMATION_SCHEMA から、テーブル情報を参照してみます。

external テーブルなので、is_insertable_into が NO で、table_type が EXTERNAL になっていることが確認できます。

google.cloud.bigquery.table.Table ｜ BigQuery ドキュメント

テーブルメタ情報（ __TABLES__ ）も確認してみます。

type が 3 の external テーブルのレコードは確認できましたが、row_count と size_bytes は 0 になっています。データ本体を BigQuery にロードしていない外部テーブルの場合は、サイズやレコード数をメタテーブルで取得することはできないようです。

外部テーブルに対するクエリの料金

データの実態は外部ストレージに格納されているため、BigQuery 側のストレージ課金は発生しませんが、BigQuery で実行したクエリの処理データサイズに対するクエリ課金は発生します。

クエリ課金では、たとえ LIMIT で結果件数を絞ったとしても、クエリ実行に必要なデータ量全体に対して課金されるため、データ量が多いテーブルに対して SELECT * などのクエリを実行すると料金が増加してしまいます。外部データソースでストレージ課金が発生しないとはいえ、実行クエリのコストチューニングは必要です。

また、外部テーブルに対するクエリ課金の処理データサイズは、外部ストレージの格納サイズではなく、BigQuery が実際に処理する際のデータ型のサイズで算出されるということなので、特に外部ストレージに圧縮データを格納している場合などには注意が必要です。